set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table ai_group.yl_oms_oms_order
select
     id
    ,customer_order_id
    ,order_source_code
    ,order_source_name
    ,order_type_code
    ,waybill_id
    ,origin_id
    ,origin_code
    ,origin_name
    ,case when instr(sender_name,'*') > 0 then sender_name else concat(regexp_replace(substr(trim(sender_name),1,length(trim(sender_name))-1),'[^.]','*'),substr(trim(sender_name),-1,1)) end as sender_name --寄件人姓名 显示最后1个字，其余使用*替换·适用于中文姓名
    ,sender_company
    ,case when instr(sender_mobile_phone,'*') > 0 then sender_mobile_phone else concat(substr(trim(sender_mobile_phone),1,3),'****',substr(trim(sender_mobile_phone),-4,4)) end as sender_mobile_phone --寄件人手机号 显示前3位和后4位，其余显示*,适用于大陆手机号
    ,case when instr(sender_telphone,'*') > 0 then sender_telphone else concat(regexp_replace(substr(trim(sender_telphone),1,length(trim(sender_telphone))-4),'[^.]','*'),substr(trim(sender_telphone),-4,4)) end as sender_telphone --展示后4位，其余显示*·适用于大陆固定电话
    ,sender_country_id
    ,sender_country_name
    ,sender_province_id
    ,sender_province_name
    ,sender_city_id
    ,sender_city_name
    ,sender_area_id
    ,sender_area_name
    ,sender_township
    ,sender_street
    ,sender_detailed_address
    ,sender_postal_code
    ,sender_email
    ,is_privacy
    ,phone_contact
    ,receiver_name
    ,receiver_company
    ,receiver_mobile_phone
    ,receiver_telphone
    ,destination_id
    ,destination_code
    ,destination_name
    ,receiver_country_id
    ,receiver_country_name
    ,receiver_province_id
    ,receiver_province_name
    ,receiver_city_id
    ,receiver_city_name
    ,receiver_area_id
    ,receiver_area_name
    ,receiver_township
    ,receiver_street
    ,receiver_detailed_address
    ,receiver_postal_code
    ,receiver_email
    ,receiver_sorting_code
    ,terminal_dispatch_code
    ,accurate
    ,remarks
    ,express_type_code
    ,express_type_name
    ,insured
    ,declared_value
    ,insured_value
    ,cod_need
    ,cod_money
    ,cod_fee
    ,cod_currency_type_code
    ,cod_currency_type_name
    ,goods_type_code
    ,goods_type_name
    ,goods_name
    ,package_number
    ,package_length
    ,package_wide
    ,package_high
    ,packate_volume
    ,package_charge_weight
    ,package_total_weight
    ,box_standard_id
    ,box_standard_code
    ,box_standard_name
    ,box_price
    ,standard_value
    ,receivable_freight
    ,discount_freight
    ,total_freight
    ,payment_mode_code
    ,payment_mode_name
    ,paid_mode_id
    ,paid_mode_code
    ,paid_mode_name
    ,customer_code
    ,customer_name
    ,spm_money_type
    ,send_code
    ,send_name
    ,dispatch_code
    ,dispatch_name
    ,dispatch_number
    ,customer_order_time
    ,input_time
    ,order_status_code
    ,cancel_reason
    ,cancel_time
    ,pick_fail_time
    ,pick_fail_reason
    ,prints_number
    ,best_pick_time_start
    ,best_pick_time_end
    ,real_pick_network_id
    ,real_pick_network_code
    ,real_pick_network_name
    ,dispatch_network_time
    ,pick_network_id
    ,pick_network_code
    ,pick_network_name
    ,dispatch_staff_time
    ,pick_staff_code
    ,pick_staff_name
    ,pick_time
    ,last_dispatch_network_id
    ,fourth_segment_code
    ,receiver_encrypt_phone
    ,last_dispatch_network_time
    ,dispatch_network_reason
    ,receiver_encrypt_telphone
    ,last_dispatch_staff_name
    ,last_dispatch_staff_time
    ,assigner_name
    ,assigner_mobile_phone
    ,goods_value
    ,express_type_id
    ,goods_type_id
    ,payment_mode_id
    ,box_number
    ,member_id
    ,sender_country_code
    ,receiver_country_code
    ,customer_id
    ,update_by
    ,update_by_name
    ,update_time
    ,last_update_time_sync
    ,is_delete
    ,is_plaintext
    ,create_by
    ,create_by_code
    ,create_by_name
    ,back_dispatch_network_number
    ,back_dispatch_network_reason
    ,back_dispatch_network_time
    ,settlement_weight
    ,dispatch_network_id
    ,cancel_reason_code
    ,need_dispatch
    ,proxy_area_id
    ,proxy_area_code
    ,proxy_area_name
    ,cancel_explain
    ,dispatch_proxy_area_time
    ,is_real_name
    ,real_name
    ,id_no_type
    ,case when instr(id_no,'*') > 0 then id_no else concat(substr(trim(id_no),1,1),regexp_replace(substr(trim(id_no),2,length(trim(id_no))-5),'[^.]','*'),substr(trim(id_no),-4,4)) end as id_no --·显示前1位和后4位，其余显示*·适用于15位和18位身份证
    ,sex
    ,exception_msg
    ,subscribe_source_code
    ,package_volume
    ,customer_waybill_no
    ,is_business
    ,print_status_code
    ,second_code_hit_desc
    ,dispatch_network_code
    ,dispatch_network_name
    ,is_can_cancel
    ,sign_receipt
    ,subscribe_source_name
    ,last_center_code
    ,last_center_name
    ,receipt_freight
    ,is_transfer
    ,dt
from jms_ods.yl_oms_oms_order
where dt = '{{ execution_date | cst_ds }}'



